/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.yournamehere.server;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.hp.hpl.jena.db.DBConnection;
import com.hp.hpl.jena.db.IDBConnection;
import com.hp.hpl.jena.query.Query;
import com.hp.hpl.jena.query.QueryExecution;
import com.hp.hpl.jena.query.QueryExecutionFactory;
import com.hp.hpl.jena.query.QueryFactory;
import com.hp.hpl.jena.query.QuerySolution;
import com.hp.hpl.jena.query.ResultSet;
import com.hp.hpl.jena.query.Syntax;
import com.hp.hpl.jena.rdf.model.Literal;
import com.hp.hpl.jena.rdf.model.Model;
import com.hp.hpl.jena.rdf.model.ModelFactory;
import com.hp.hpl.jena.rdf.model.ModelMaker;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.yournamehere.client.getDataSparql;

/**
 *
 * @author Cumar C.
 */
public class getDataSparqlImpl extends RemoteServiceServlet implements getDataSparql {

    @Override
    public String getDocentesRecomendados(String s) {

        String data = "";
        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://" + "127.0.0.1" + "/" + "jenastores";
            Class.forName(driver).newInstance();
            IDBConnection connection = new DBConnection(url, "root", "", "MySQL");
            ModelMaker maker = ModelFactory.createModelRDBMaker(connection);
            Model model = maker.openModel("AsignacionDocentes");
            String queryString = "PREFIX asig:<http://localhost/AsignacionDocentes.owl#> "
                    + " SELECT ?DocNombre  ?DocCedula "
                    + "  WHERE   "
                    + " {     "
                    + " ?Asignaturas a asig:ASIGNATURA; "
                    + "              asig:nombre ?AsigNombre; "
                    + "              asig:perteneceA ?ObjArea. "
                    + " ?objDocente a asig:DOCENTE; "
                    + "             asig:trabajaEn ?ObjArea; "
                    + "             asig:nombre ?DocNombre; "
                    + "             asig:cedula ?DocCedula. "
                    + " FILTER (?AsigNombre = '" + s + "') "
                    + " }";

            Query query = QueryFactory.create(queryString);
            QueryExecution qexec = QueryExecutionFactory.create(query, model);
            try {
                ResultSet results = qexec.execSelect();
                while (results.hasNext()) {
                    QuerySolution soln = results.nextSolution();
                    Literal docNomb = soln.getLiteral("DocNombre");
                    Literal docCed = soln.getLiteral("DocCedula");

                    data += docNomb.getValue().toString() + "@"
                            + docCed.getValue().toString() + "#";
                }
            } finally {
                qexec.close();
            }
        } catch (InstantiationException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;

    }

    /**
     * Obtiene el area con el numero de docentes que trabaja en cada uno de estas
     * @return String
     */
    private String consultarNumeroDocentesAreas() {
        String data = "";

        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://" + "127.0.0.1" + "/" + "jenastores";
            Class.forName(driver).newInstance();
            IDBConnection connection = new DBConnection(url, "root", "", "MySQL");
            ModelMaker maker = ModelFactory.createModelRDBMaker(connection);
            Model model = maker.openModel("AsignacionDocentes");
            String queryString = "PREFIX ont:<http://localhost/AsignacionDocentes.owl#> "
                    + "PREFIX w3:<http://www.w3.org/1999/02/22-rdf-syntax-ns#> "
                    + "SELECT ?nombre (count(?docente) as ?docentes) "
                    + "WHERE{ "
                    + "?areas w3:type ont:AREA . "
                    + "?areas ont:nombre ?nombre . "
                    + "?docente ont:trabajaEn ?areas "
                    + "} "
                    + "GROUP BY ?nombre "
                    + "ORDER BY ASC(?nombre)";

            Query query = QueryFactory.create(queryString, Syntax.syntaxARQ);
            QueryExecution qexec = QueryExecutionFactory.create(query, model);
            try {
                ResultSet results = qexec.execSelect();
                while (results.hasNext()) {
                    QuerySolution soln = results.nextSolution();
                    Literal area = soln.getLiteral("?nombre");
                    Literal n_docente = soln.getLiteral("?docentes");

                    data += area.getValue().toString() + "@"
                            + n_docente.getValue().toString() + "@" + "#";
                }
            } finally {
                qexec.close();
            }
        } catch (InstantiationException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return data;
    }

    /**
     * Consulta el numero de paralelos en los que tienen que dar los profesores
     * dependiendo del area en la que trabajen
     * @return String
     */
    private String consultarNumeroParalelosAreas() {
        String data = "";

        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://" + "127.0.0.1" + "/" + "jenastores";
            Class.forName(driver).newInstance();
            IDBConnection connection = new DBConnection(url, "root", "", "MySQL");
            ModelMaker maker = ModelFactory.createModelRDBMaker(connection);
            Model model = maker.openModel("AsignacionDocentes");
            String queryString = "PREFIX ont:<http://localhost/AsignacionDocentes.owl#> "
                    + "PREFIX w3:<http://www.w3.org/1999/02/22-rdf-syntax-ns#> "
                    + "SELECT ?nombre (count(?paralelo) as ?paralelos) "
                    + "WHERE{ "
                    + "?areas w3:type ont:AREA . "
                    + "?areas ont:nombre ?nombre . "
                    + "?mat ont:perteneceA ?areas . "
                    + "?mat ont:paralelos ?paralelo "
                    + "} "
                    + "GROUP BY ?nombre "
                    + "ORDER BY ASC(?nombre)";

            Query query = QueryFactory.create(queryString, Syntax.syntaxARQ);
            QueryExecution qexec = QueryExecutionFactory.create(query, model);
            try {
                ResultSet results = qexec.execSelect();
                while (results.hasNext()) {
                    QuerySolution soln = results.nextSolution();
                    Literal area = soln.getLiteral("?nombre");
                    Literal n_docente = soln.getLiteral("?paralelos");

                    data += area.getValue().toString() + "@"
                            + n_docente.getValue().toString() + "@" + "#";
                }
            } finally {
                qexec.close();
            }
        } catch (InstantiationException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return data;
    }

    @Override
    public String getDatosAreas() {
        ArrayList<Areas> listaAreas = new ArrayList<Areas>();
        String N_Docentes = consultarNumeroDocentesAreas();
        String[] filasDocentes = N_Docentes.split("#");

        for (String fil : filasDocentes) {
            String[] columnas = fil.split("@");
            Areas area = new Areas(columnas[0], Integer.parseInt(columnas[1]));
            listaAreas.add(area);
        }

        String N_Paralelos = consultarNumeroParalelosAreas();
        String[] filasParalelos = N_Paralelos.split("#");
        for (String fil : filasParalelos) {
            String[] columnas = fil.split("@");

            for (int i = 0; i < listaAreas.size(); i++) {
                if (listaAreas.get(i).getArea().equals(columnas[0])) {
                    listaAreas.get(i).setN_paralelos(Integer.parseInt(columnas[1]));
                }
            }
        }

        String presentar = "";

        int resta;
        if (listaAreas.size() > 0) {
            for (Areas area : listaAreas) {
                System.out.println("Area:" + area.getArea());
                System.out.println("Paralelos:" + area.getN_paralelos());
                System.out.println("Profesores:" + area.getN_profesores());
                resta = (area.getN_paralelos() - (area.getN_profesores() * 3));

                if (resta > 0) {
                    String def = "<div class=\"texto\">Esta área cuenta con un deficit de docentes, se recomienda <br/>incluir:  " + resta + " docentes</div>";
                    String fila = "<div class=\"area\"><b>Area: " + area.getArea() + "</b><br/>" + def + "</div>";
                    presentar += fila;
                }
            }
        } else {
            presentar = "<div class=\"area\">No hay datos que mostrar... :-(</div>";
        }

        return presentar;
    }

    @Override
    public String getNombreAsignaturas() {
        String data = "";
        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://" + "127.0.0.1" + "/" + "jenastores";
            Class.forName(driver).newInstance();
            IDBConnection connection = new DBConnection(url, "root", "", "MySQL");
            ModelMaker maker = ModelFactory.createModelRDBMaker(connection);
            Model model = maker.openModel("AsignacionDocentes");
            String queryString = "PREFIX asig:<http://localhost/AsignacionDocentes.owl#>  "
                    + " SELECT ?AsigNombre "
                    + "  WHERE   "
                    + " {     "
                    + " ?Asignaturas a asig:ASIGNATURA; "
                    + "              asig:nombre ?AsigNombre ; "
                    + " }";

            Query query = QueryFactory.create(queryString);
            QueryExecution qexec = QueryExecutionFactory.create(query, model);
            try {
                ResultSet results = qexec.execSelect();
                while (results.hasNext()) {
                    QuerySolution soln = results.nextSolution();
                    Literal asigName = soln.getLiteral("AsigNombre");
                    data += asigName.getValue().toString() + "#";
                }
            } finally {
                qexec.close();
            }
        } catch (InstantiationException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
    }

    /**
     * Los dias libres de la carrera para realizar un seminario o alguna charla
     * @return String
     */
    @Override
    public String getDiasSeminarios() {
        String data = "<div class=\"texto\">Un buen día para realizar un seminario para la carrera sin interferir con las clases es:</div>";
        String bim1 = "<div class=\"area\">Primer Bimestre</div>";
        String bim2 = "<div class=\"area\">Segundo Bimestre</div>";
        ArrayList<String> diasSemana = new ArrayList<String>();
        diasSemana.add("Lunes");
        diasSemana.add("Martes");
        diasSemana.add("Miercoles");
        diasSemana.add("Jueves");
        diasSemana.add("Viernes");

        ArrayList<String> dias1Bim = diasSemana;
        ArrayList<String> dias2Bim = diasSemana;

        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://" + "127.0.0.1" + "/" + "jenastores";
            Class.forName(driver).newInstance();
            IDBConnection connection = new DBConnection(url, "root", "", "MySQL");
            ModelMaker maker = ModelFactory.createModelRDBMaker(connection);
            Model model = maker.openModel("AsignacionDocentes");
            String queryString = "PREFIX ont:<http://localhost/AsignacionDocentes.owl#> "
                    + "PREFIX w3:<http://www.w3.org/1999/02/22-rdf-syntax-ns#> "
                    + "SELECT ?n_bimestre ?dia "
                    + "WHERE{ "
                    + "?materias w3:type ont:ASIGNATURA ; "
                    + "	  ont:dictadaEn ?bimestre ; "
                    + "	  ont:diaSemana	?dia . "
                    + "?bimestre ont:orden ?n_bimestre "
                    + "} "
                    + "GROUP BY ?n_bimestre ?dia "
                    + "ORDER BY ?n_bimestre";

            Query query = QueryFactory.create(queryString);
            QueryExecution qexec = QueryExecutionFactory.create(query, model);
            try {
                ResultSet results = qexec.execSelect();
                while (results.hasNext()) {
                    QuerySolution soln = results.nextSolution();
                    Literal dias = soln.getLiteral("dia");
                    Literal bimestre = soln.getLiteral("n_bimestre");
//                    data += bimestre.getInt()+ "@"+ dias.getValue().toString() + "@" + "#";
                    if (bimestre.getInt() == 1) {
                        dias1Bim.remove(dias.getValue().toString());
                    } else {
                        dias2Bim.remove(dias.getValue().toString());
                    }
                }
            } finally {
                qexec.close();
            }
            for (String dia : dias1Bim) {
                bim1 += "<div class=\"dia\">" + dia + "</div>";
            }
            for (String dia : dias2Bim) {
                bim2 += "<div class=\"dia\">" + dia + "</div>";
            }
            data += bim1 + bim2;
        } catch (InstantiationException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(getDataSparqlImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
    }
}
